package com.xenstra.rhodiumone.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.xenstra.rhodiumone.util.CommonUtil;
import com.xenstra.rhodiumone.vo.EquityVO;
import com.xenstra.rhodiumone.vo.FutureVO;
import com.xenstra.rhodiumone.vo.OptionVO;

public class RhodiumOneDAO {

	private Connection connection;
	
	public RhodiumOneDAO(Connection connection){
		this.connection = connection;
	}
	
	/**
	 * Returns a list of all the equities available.
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<EquityVO> getAllEquities(){
		List<EquityVO> result = new LinkedList<EquityVO>();
		String sql = "SELECT SYMBOL, NAME FROM whitelist ORDER BY NAME";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(new EquityVO(rs.getString(1), rs.getString(2)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs);
			close(pstmt);			
		}
		
		return result;
	}
	
	/**
	 * Returns a list of expiry dates of the futures and options for which data is available.
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Date> getAllExpiryDates(){
		List<Date> result = new LinkedList<Date>();
		String sql = "SELECT EXPIRY_DATE FROM expiry_dates ORDER BY EXPIRY_DATE";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(rs.getDate(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(rs);
			close(pstmt);
		}

		return result;
	}

	/**
	 * Returns a list of available strike prices for a given equity/index, option type, and expiry date.
	 *  
	 * @param symbol
	 * @param optionType
	 * @param expiryDate
	 * @return
	 */
	public List<Double> getOptionsStrikePrices(String symbol, String optionType, Date expiryDate){
		String sql = "SELECT DISTINCT STRIKE_PR FROM main_options"
				+ " WHERE symbol = ?"
				+ " AND OPTION_TYP = ?"
				+ " AND EXPIRY_DT = ?"
				+ " ORDER BY STRIKE_PR";
		
		List<Double> result = new LinkedList<Double>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, optionType);
			pstmt.setString(3, CommonUtil.formatDate(expiryDate));
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(rs.getDouble(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs);
			close(pstmt);			
		}
		
		return result;
	}

	public List<OptionVO> getOptionsData(String symbol, String optionType, Date expiryDate, double strikePrice){
		String sql = "SELECT id, SYMBOL, EXPIRY_DT, STRIKE_PR, OPTION_TYP, OPEN, HIGH, LOW, CLOSE, SETTLE_PR, TIMESTAMP"
				+ " FROM main_options"
				+ " WHERE SYMBOL = ?"
				+ " AND OPTION_TYP = ?"
				+ " AND EXPIRY_DT = ?"
				+ " AND STRIKE_PR = ?"
				+ " ORDER BY TIMESTAMP";
		
		List<OptionVO> result = new LinkedList<OptionVO>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, optionType);
			pstmt.setString(3, CommonUtil.formatDate(expiryDate));
			pstmt.setDouble(4, strikePrice);
			rs = pstmt.executeQuery();
			
			fillOptionsData(result, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs);
			close(pstmt);			
		}
		
		return result;
	}

	
	public List<FutureVO> getFuturesData(String symbol,Date expiryDate){
		String sql = "SELECT id, SYMBOL, EXPIRY_DT, OPEN, HIGH, LOW, CLOSE, SETTLE_PR, TIMESTAMP"
				+ " FROM main_futures"
				+ " WHERE SYMBOL = ?"
				+ " AND EXPIRY_DT = ?"
				+ " ORDER BY TIMESTAMP";
		
		List<FutureVO> result = new LinkedList<FutureVO>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, symbol);
			pstmt.setString(2, CommonUtil.formatDate(expiryDate));
			rs = pstmt.executeQuery();
			
			fillFuturesData(result, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs);
			close(pstmt);			
		}
		
		return result;
	}


	public List<EquityVO> executeAdHocEquityQuery(String sql){
		List<EquityVO> result = new LinkedList<EquityVO>();
		
		Statement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.createStatement();
			rs = pstmt.executeQuery(sql);
			
			fillEquityData(result, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs);
			close(pstmt);			
		}
		
		return result;
	}

	public List<OptionVO> executeAdHocOptionsQuery(String sql){
		List<OptionVO> result = new LinkedList<OptionVO>();
		
		Statement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.createStatement();
			rs = pstmt.executeQuery(sql);
			
			fillOptionsData(result, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs);
			close(pstmt);			
		}
		
		return result;
	}
	
	public List<FutureVO> executeAdHocFuturesQuery(String sql){
		List<FutureVO> result = new LinkedList<FutureVO>();
		
		Statement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = connection.createStatement();
			rs = pstmt.executeQuery(sql);
			
			fillFuturesData(result, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs);
			close(pstmt);			
		}
		
		return result;
	}
	
	//////////////////////// Utility methods //////////////////////
	
	private void fillEquityData(List<EquityVO> result, ResultSet rs)throws SQLException {
		while(rs.next()){
			EquityVO vo = new EquityVO(
					rs.getLong("id"), 
					rs.getString("SYMBOL"), 
					rs.getDouble("OPEN"), 
					rs.getDouble("HIGH"),
					rs.getDouble("LOW"), 
					rs.getDouble("CLOSE"), 
					rs.getDouble("PREVCLOSE"), 
					rs.getDate("TIMESTAMP")
				);
			
			result.add(vo);
		}
	}
	
	private void fillOptionsData(List<OptionVO> result, ResultSet rs)throws SQLException {
		while(rs.next()){
			OptionVO vo = new OptionVO(
					rs.getLong("id"),
					rs.getString("SYMBOL"),
					rs.getDate("EXPIRY_DT"),
					rs.getDouble("STRIKE_PR"),
					rs.getString("OPTION_TYP"),
					rs.getDouble("OPEN"),
					rs.getDouble("HIGH"),
					rs.getDouble("LOW"),
					rs.getDouble("CLOSE"),
					rs.getDouble("SETTLE_PR"),
					rs.getDate("TIMESTAMP")
				);
			
			result.add(vo);
		}
	}
	
	private void fillFuturesData(List<FutureVO> result, ResultSet rs)throws SQLException {
		while(rs.next()){
			FutureVO vo = new FutureVO(
					rs.getLong("id"),
					rs.getString("SYMBOL"),
					rs.getDate("EXPIRY_DT"),
					rs.getDouble("OPEN"),
					rs.getDouble("HIGH"),
					rs.getDouble("LOW"),
					rs.getDouble("CLOSE"),
					rs.getDouble("SETTLE_PR"),
					rs.getDate("TIMESTAMP")
				);
			
			result.add(vo);
		}
	}
	
	private void close(ResultSet rs){
		try {
			if(rs != null && ! rs.isClosed()){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void close(Statement pstmt){
		try {
			if(pstmt != null && ! pstmt.isClosed()){
				pstmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
